package org.budo.excel.jdbc.driver.statement.impl;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.budo.excel.jdbc.driver.BudoExcelJdbcConnection;
import org.budo.excel.jdbc.driver.executor.PoiExcelStatementExecutor;
import org.budo.excel.jdbc.driver.statement.AbstractExcelStatement;
import org.budo.jdbc.driver.AbstractBudoJdbcPreparedStatement;
import org.budo.jdbc.driver.resultset.impl.MapArrayResultSet;
import org.budo.poi.util.WorkbookUtil;
import org.budo.support.jsqlparser.JSqlParserUtil;
import org.budo.support.lang.util.IoUtil;

import net.sf.jsqlparser.statement.Statement;

/**
 * @author limingwei
 */
public class SelectStatement extends AbstractExcelStatement {
    @Override
    public void execute(PreparedStatement preparedStatement) throws SQLException {
        Connection connection = preparedStatement.getConnection();
        BudoExcelJdbcConnection excelJdbcConnection = (BudoExcelJdbcConnection) connection;
        PoiExcelStatementExecutor excelStatementExecutor = (PoiExcelStatementExecutor) excelJdbcConnection.statementExecutor();
        String url = excelStatementExecutor.getJdbcUrl().getUrl();
        String path = url.replace("jdbc:excel://", "");

        FileInputStream inputStream = IoUtil.fileInputStream(path);
        Workbook workbook = WorkbookUtil.newWorkbook(inputStream);

        Statement statement = JSqlParserUtil.parse(this.getSql());
        String tableName = JSqlParserUtil.getTableName(statement);

        Sheet sheet = workbook.getSheet(tableName);
        int rowCount = sheet.getPhysicalNumberOfRows();

        if (rowCount < 1) {
            this.handleEmpty(preparedStatement);
            return;
        }

        String[] columnNames = this.columnNames(sheet);
        Map[] valueArray = this.valueArray(sheet, columnNames);

        MapArrayResultSet resultSet = new MapArrayResultSet(columnNames, valueArray);
        ((AbstractBudoJdbcPreparedStatement) preparedStatement).setResultSet(resultSet);

        WorkbookUtil.close(workbook);
    }

    private String[] columnNames(Sheet sheet) {

        Row titleRow = sheet.getRow(0);
        int columnCount = titleRow.getPhysicalNumberOfCells();

        // 第0行为列名
        List<String> columnNames = new ArrayList<String>(columnCount);

        for (int j = 0; j < columnCount; j++) {
            Cell cell = titleRow.getCell(j);
            columnNames.add(cell + "");
        }

        return columnNames.toArray(new String[columnCount]);
    }

    private Map[] valueArray(Sheet sheet, String[] columnNames) {
        int rowCount = sheet.getPhysicalNumberOfRows();
        int columnCount = columnNames.length;

        List<Map> valueArray = new ArrayList<Map>(rowCount);

        // 第1行往下为数据
        for (int i = 1; i < rowCount; i++) {
            Row dataRow = sheet.getRow(i);

            Map valueMap = new HashMap();
            for (int j = 0; j < columnCount; j++) {
                Cell cell = dataRow.getCell(j);

                valueMap.put(columnNames[i], cell + "");
            }

            valueArray.add(valueMap);
        }

        return valueArray.toArray(new Map[rowCount]);
    }

    private void handleEmpty(PreparedStatement preparedStatement) {
        MapArrayResultSet resultSet = new MapArrayResultSet();
        ((AbstractBudoJdbcPreparedStatement) preparedStatement).setResultSet(resultSet);
    }
}